package com.zcsy.manage.web.tools;

import java.io.OutputStream;

import java.lang.reflect.Method;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.log4j.Logger;


/**
 * @Title: JExcelReport.java
 * @Package： com.zcsy.manage.web.content 
 * @Description: 用于生产excel文件的相关方法 〈功能详细描述〉
 * @author yangyang yang1192618843@163.com
 * @Copyright: 2015 武汉中财数元信息技术有限公司 All rights reserved.
 * @date 2015年9月18日
 * @version 1.0
 * @see JDK1.7.0_75
 * @since
 */
public class JExcelReport {

    private final Logger logger = Logger.getLogger(JExcelReport.class);

    public JExcelReport() {}

    /**
     * 说明：根据列表数据导出到exel文件，此方法仅支持包含单个sheet的excel文件
     * 
     * @param sheet
     *            sheetName sheet页的名称，可为空，但建议不要为空
     * @param ArrayList
     *            <ArrayList<String>> arrayList 封装的数据，即用于生产excel的数据，其格式为
     *            arrayList---------------------- |----ArrayLisy<String> 行数据 |----ArrayLisy<String>
     *            行数据 |----ArrayLisy<String> 行数据 |........ arrayList中存储的
     *            元素为ArrayLisy<String>，即每一行的数据
     * @param row
     *            String[] 列的标题，用于存储excel文件第一列的标题
     * @param os
     *            OutputStream
     * @param int[] style 列样式数据，用来描述每一列的宽度 可为空
     * @param int titleHeight 行样式数据，用来描述标题栏的高度
     * @return void
     * @throws Exception
     */
    public void excelreport(String sheetName, ArrayList<ArrayList<String>> arrayList,
                            String[] row, OutputStream os, int[] style)
        throws Exception {
        // 检查列名是否存在
        int width = row.length;
        if (width == 0) {
            throw new Exception("please input excel file columns name!");
        }

        // 检查是否有数据,如果数据为空，则声称一列临时数据
        int size = arrayList.size();
        if (size == 0) {
            logger.info("content data can't be null or empty!");

            arrayList = new ArrayList<ArrayList<String>>(1);
            ArrayList<String> temp = new ArrayList<String>();

            String string = "---";
            for (int i = 0; i < width; i++ ) {
                temp.add(string);
            }
            arrayList.add(temp);
        }

        try {

            // 设置sheet
            WritableWorkbook workbook = Workbook.createWorkbook(os);

            if ("".equals(sheetName)) {
                sheetName = "sheet1";
            }

            // 设置单元格格式
            jxl.write.WritableSheet writesheet = workbook.createSheet(sheetName, 0);
            writesheet.setRowView(0, 400);
            // 如果用户传递了列样式，则按照用户的样式生产
            if (style != null) {
                for (int i = 0; i < style.length; i++ ) {
                    writesheet.setColumnView(i, style[i]);
                }
            } else {
                writesheet.setColumnView(0, 8);
                writesheet.setColumnView(1, 20);
                writesheet.setColumnView(2, 20);
                writesheet.setColumnView(3, 20);
                writesheet.setColumnView(4, 20);
                writesheet.setColumnView(5, 20);
                writesheet.setColumnView(6, 20);
                writesheet.setColumnView(7, 20);
                writesheet.setColumnView(8, 20);
                writesheet.setColumnView(9, 20);
            }

            // 设置首行高度
            writesheet.setRowView(0, 500);

            // 可以定义模板格式化你的cell
            // 添加字体样式
            /**
             * 第一个: TIMES是字体大小 第二个: BOLD是判断是否为斜体,选择true时为斜体 第三个: ARIAL 第四个:
             * UnderlineStyle.NO_UNDERLINE 下划线 第五个: jxl.format.Colour.RED 字体颜色是红色的
             * 此方法算是一个容器，可以放进去好多属性
             */
            jxl.write.WritableFont writefont = new jxl.write.WritableFont(WritableFont.TIMES,
                WritableFont.DEFAULT_POINT_SIZE);
            jxl.write.WritableFont writetitle = new jxl.write.WritableFont(WritableFont.ARIAL, 8,
                WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);

            jxl.write.WritableCellFormat writecellfromat = new jxl.write.WritableCellFormat(
                writefont);
            jxl.write.WritableCellFormat wcf_header = new jxl.write.WritableCellFormat(writetitle);

            // 设置单元格格式
            writecellfromat.setAlignment(jxl.format.Alignment.CENTRE); // 水平居中
            writecellfromat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 垂直居中
            writecellfromat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,
                jxl.format.Colour.BLACK);
            writecellfromat.setWrap(true);

            // 设置表头的格式
            wcf_header.setAlignment(jxl.format.Alignment.CENTRE); // 水平居中
            wcf_header.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 垂直居中
            wcf_header.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,
                jxl.format.Colour.BLACK);
            wcf_header.setWrap(true);
            wcf_header.setBackground(jxl.format.Colour.GREY_25_PERCENT);

            /**
             * 在Label()方法里面有三个参数 第一个是代表列数 第二是代表行数 第三个代表要写入的内容 第四个是可选项，是输入这个label里面的样式
             * 然后通过写sheet的方法addCell（）把内容写进sheet里面。
             */
            // 添加大列
            Label label = null;
            // 添加具体列名
            for (int j = 0; j < width; j++ ) {
                label = new Label(j, 0, row[j], wcf_header);
                writesheet.addCell(label);
            }

            // 添加具体内容
            for (int j = 0; j < size; j++ ) {
                writesheet.setRowView(j + 1, 350);
                ArrayList arr = (ArrayList)arrayList.get(j);
                if (arr != null && arr.size() > 0) {
                    int length = arr.size();
                    for (int k = 0; k < length; k++ ) {
                        String value = "--";
                        if (null != arr.get(k)) {
                            value = String.valueOf(arr.get(k));
                        }
                        if ("".equals(value) || "null".equals(value)) {
                            value = "--";
                        }
                        label = new Label(k, j + 1, value, writecellfromat);
                        writesheet.addCell(label);
                    }
                }
            }
            
            if (sheetName.contains("进度统计清单")) {
                if (size >= 1) {
                    int total = 0;
                    int deliver = 0; 
                    int install = 0;
                    int open = 0;
                    int startInspectOne = 0;
                    int startAssertManaged = 0;
                    int filing = 0;
                    int needPicture = 0;
                    int needCheckInfo = 0;
                    int towerHandover = 0;
                    int towerConfirm = 0;
                    int payed = 0;
                    int recheck = 0;
                    NumberFormat nt = NumberFormat.getPercentInstance();
                  //设置百分数精确度2即保留两位小数
                    nt.setMinimumFractionDigits(2);
                    for (int i = 0; i < size; i ++) {
                        ArrayList arr = (ArrayList)arrayList.get(i);
                        total += Integer.valueOf(arr.get(2).toString());
                        deliver += Integer.valueOf(arr.get(3).toString());
                        install += Integer.valueOf(arr.get(4).toString());
                        open += Integer.valueOf(arr.get(6).toString());
                        startInspectOne += Integer.valueOf(arr.get(9).toString());
                        startAssertManaged += Integer.valueOf(arr.get(10).toString());
                        filing += Integer.valueOf(arr.get(11).toString());
                        needPicture += Integer.valueOf(arr.get(13).toString());
                        needCheckInfo += Integer.valueOf(arr.get(14).toString());
                        towerHandover += Integer.valueOf(arr.get(15).toString());
                        payed += Integer.valueOf(arr.get(16).toString());
                        towerConfirm += Integer.valueOf(arr.get(17).toString());
                        recheck += Integer.valueOf(arr.get(18).toString());
                    }
                  //统计率
                    String installRate = null, openRate = null, totalOpenRate = null, 
                        filingRate = null, payedRate = null, recheckRate = null;
                    
                    //发货安装率
                    if (deliver == 0) installRate = "0.00%";
                    else installRate = nt.format((float)install / deliver);
                    //安装开通率
                    if (install == 0) openRate = "0.00%";
                    else openRate = nt.format((float)open / install);
                    //县市开通率
                    totalOpenRate = nt.format((float)open / total);
                    //归档率
                    if (filing == 0) filingRate = "0.00%";
                    else filingRate = nt.format((float)filing / total);
                    //铁塔支付率
                    if (payed == 0) payedRate = "0.00%";
                    else payedRate = nt.format((float)payed / towerHandover);
                    //复审率
                    if (recheck == 0) recheckRate = "0.00%";
                    else recheckRate = nt.format((float)recheck / towerHandover);
                    
                    writesheet.setRowView(size + 1, 500);
                    writesheet.addCell(new Label(0, size + 1, "合计", writecellfromat));
                    writesheet.addCell(new Label(2, size + 1, String.valueOf(total), writecellfromat));
                    writesheet.addCell(new Label(3, size + 1, String.valueOf(deliver), writecellfromat));
                    writesheet.addCell(new Label(4, size + 1, String.valueOf(install), writecellfromat));
                    writesheet.addCell(new Label(5, size + 1, installRate, writecellfromat));
                    writesheet.addCell(new Label(6, size + 1, String.valueOf(open), writecellfromat));
                    writesheet.addCell(new Label(7, size + 1, openRate, writecellfromat));
                    writesheet.addCell(new Label(8, size + 1, totalOpenRate, writecellfromat));
                    writesheet.addCell(new Label(9, size + 1, String.valueOf(startInspectOne), writecellfromat));
                    writesheet.addCell(new Label(10, size + 1, String.valueOf(startAssertManaged), writecellfromat));
                    writesheet.addCell(new Label(11, size + 1, String.valueOf(filing), writecellfromat));
                    writesheet.addCell(new Label(12, size + 1, filingRate, writecellfromat));
                    writesheet.addCell(new Label(13, size + 1, String.valueOf(needPicture), writecellfromat));
                    writesheet.addCell(new Label(14, size + 1, String.valueOf(needCheckInfo), writecellfromat));
                    writesheet.addCell(new Label(15, size + 1, String.valueOf(towerHandover), writecellfromat));
                    writesheet.addCell(new Label(16, size + 1, String.valueOf(towerConfirm), writecellfromat));
                    writesheet.addCell(new Label(17, size + 1, String.valueOf(payed), writecellfromat));
                    writesheet.addCell(new Label(18, size + 1, String.valueOf(recheck), writecellfromat));
                    writesheet.addCell(new Label(19, size + 1, payedRate, writecellfromat));
                    writesheet.addCell(new Label(20, size + 1, recheckRate, writecellfromat));
                    writesheet.mergeCells(0, size + 1, 1, size + 1);  //合并单元格
                }
            }
            
            workbook.write();
            workbook.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 说明：根据列表数据导出到exel文件，本方法用于同时生产多个sheet的excel文件
     * 
     * @param sheet
     *            String[] sheet页的名称，如果有多个sheet，则数组中有多个元素
     * @param obj
     *            ArrayList 所需要生产的数据
     * @param row
     *            String[]
     * @param os
     *            OutputStream
     */
    public void multiExcelreport(String[] sheet, ArrayList arrayList, String[] row, OutputStream os) {}

    /**
     * 导出Excel
     * 
     * @param os
     *            OutputStream 输出流
     * @param List
     *            <Map<String, Object>> <List<Map<String, Object>>> list 封装的数据，即用于生产excel的数据，其格式为
     *            arrayList---------------------- |----Map<String, Object>行数据 |----Map<String,
     *            Object> 行数据 |----Map<String, Object> 行数据
     * @param titles
     *            String[] 列的标题，用于存储excel文件第一列的标题
     * @param fields
     *            String[] map中存储的key数组，用于循环list时，取数据
     * @param fileName
     *            导出文件名
     * @param columnLength
     *            定义各列的宽度
     * @return void
     * @throws Exception
     * @author tanghan
     * @date 2014年6月23日
     */
    public static void exportExcel(OutputStream outputStream, int columnLength[], String titles[],
                                   String fields[], List<Map<String, Object>> list, String fileName)
        throws Exception {
        try {
            // 创建文件
            WritableWorkbook book = Workbook.createWorkbook(outputStream);
            // 创建工作薄
            WritableSheet sheet = book.createSheet("sheet1", 0);
            // 设置字体样式
            jxl.write.WritableFont titleFont = new jxl.write.WritableFont(WritableFont.ARIAL, 10,
                WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.DARK_BLUE);
            // 格式化字体
            jxl.write.WritableCellFormat titleCellFormat = new jxl.write.WritableCellFormat(
                titleFont);

            for (int i = 0; i < titles.length; i++ ) {
                sheet.setColumnView(i, columnLength[i]); // 设置列的宽度
                Label label = new Label(i, 0, titles[i], titleCellFormat);
                sheet.addCell(label);
            }

            // map中的key
            String key = "";
            // 根据key得到的value
            Object value = null;
            // 将要填充的单元格
            Label label = null;

            for (int i = 0; i < list.size(); i++ ) {
                Map map = list.get(i);
                if (map != null && map.size() > 0) {
                    for (int j = 0; j < fields.length; j++ ) {
                        key = fields[j];
                        if (key != null) {
                            key = key.toUpperCase().toString();
                            value = map.get(key);
                            if (value == null || value.equals("")) {
                                value = map.get(key);
                            }
                            if (value != null && !value.equals("")) {
                                label = new Label(j, i + 1, value.toString().trim());
                                sheet.addCell(label);
                            }
                        }
                    }
                }
            }

            book.write();
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 把对象和对象的值转为map generate map from obj
     * 
     * @param obj
     * @return
     */
    @SuppressWarnings("unchecked")
    public static Map genMapFromObj(Object object, String[] fields)
        throws Exception {
        Object obj = object;
        Map map = new HashMap<Object, Object>();
        // Field[] fields = obj.getClass().getDeclaredFields();
        for (int i = 0; i < fields.length; i++ ) {
            String name = fields[i];
            Method method = obj.getClass().getMethod(
                "get" + name.substring(0, 1).toUpperCase() + name.substring(1), new Class[] {});
            Object result = method.invoke(obj, new Object[] {});
            map.put(name.toUpperCase(), result);
        }
        System.out.println(map);
        return map;
    }
    
}